﻿CREATE PROCEDURE [steve].[spSteve_RebuildContentSimilarityCache]
	@IncrementalContentId uniqueidentifier = null
AS

declare @totalWeight decimal(38,7)

select @totalWeight = SUM(RelevancyWeight)
from steve.Category

declare @rebuild table (LeftContentId uniqueidentifier, RightContentId uniqueidentifier, CategorySimilarity decimal(38,7))

insert into @rebuild
select rootCC.ContentId LeftContentId
	, commonCC.ContentId RightContentId 
	, steve.udfSteve_Similarity(@totalWeight, SUM(commonC.RelevancyWeight), leftTotal.TotalWeight, rightTotal.TotalWeight) [CategorySimilarity]
from steve.ContentCategory rootCC
	inner join steve.ContentCategory commonCC on rootCC.CategoryName = commonCC.CategoryName and rootCC.ContentId <> commonCC.ContentId
	inner join steve.Category commonC on commonC.CategoryName = commonCC.CategoryName
	inner join (
		select leftCC.ContentId
			, SUM(RelevancyWeight) [TotalWeight]
		from steve.ContentCategory leftCC
			inner join steve.Category leftC on leftC.CategoryName = leftCC.CategoryName
		where leftCC.DeleteDate is null
		group by leftCC.ContentId) leftTotal on leftTotal.ContentId = rootCC.ContentId
	inner join (
		select rightCC.ContentId
			, SUM(relevancyWeight) [TotalWeight]
		from steve.ContentCategory rightCC
			inner join steve.Category rightC on rightC.CategoryName = rightCC.CategoryName
		where rightCC.DeleteDate is null
		group by rightCC.ContentId) rightTotal on rightTotal.ContentId = commonCC.ContentId
where rootCC.DeleteDate is null
	and commonCC.DeleteDate is null
	and (@IncrementalContentId is null or rootCC.ContentId = @IncrementalContentId or commonCC.ContentId = @IncrementalContentId)
group by leftTotal.TotalWeight
	, rightTotal.TotalWeight
	, rootCC.ContentId
	, commonCC.ContentId

update ContentSimilarityCache
set CategorySimilarity = 0.0
where @IncrementalContentId is null
	or ContentId = @IncrementalContentId
	or SimilarContentId = @IncrementalContentId

update csc
set CategorySimilarity = r.CategorySimilarity
from ContentSimilarityCache csc
	inner join @rebuild r on r.LeftContentId = csc.ContentId and r.RightContentId = csc.SimilarContentId

insert into ContentSimilarityCache (ContentId, SimilarContentId, CategorySimilarity)
select r.LeftContentId, r.RightContentId, r.CategorySimilarity
from @rebuild r
	left outer join ContentSimilarityCache csc on r.LeftContentId = csc.ContentId and r.RightContentId = csc.SimilarContentId
where csc.ContentId is null

RETURN 0